package com.EVO.dao;

import com.EVO.entity.City;
import com.EVO.entity.Province;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class QueryDao {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;
    private String url = "jdbc:mysql://localhost:3306/springdb";
    private String user = "root";
    private String password = "L4213416";
    public List<Province> queryProviceList(){
        List list = new ArrayList();
        Province province = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,user,password);
            ps = conn.prepareStatement("select id,name,jiancheng,shenghui from province order by id");
            rs = ps.executeQuery();
            while (rs.next()){
                province = new Province(rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("jiancheng"),
                        rs.getString("shenghui"));
                list.add(province);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
    public List<City> queryCityList(int provinceId){
        List list = new ArrayList();
        City city = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,user,password);
            ps = conn.prepareStatement("select c.id,c.name,c.provinceId from city c join province p on p.id = ? && c.provinceid=p.id");
            ps.setInt(1,provinceId);
            rs = ps.executeQuery();
            while (rs.next()){
                city = new City(rs.getInt("id"),
                        rs.getString("name"),
                        rs.getInt("provinceId"));
                list.add(city);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return list;
    }

}
